package pms;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Vector;

import org.joda.time.DateMidnight;
import org.joda.time.DateTime;
import org.joda.time.Duration;
import org.joda.time.Interval;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import pms.Database;

public class Employee {
	
	public enum accessLevel {
		EMPLOYEE, SUPERVISOR, PAYROLL_ADMIN, MANAGER;
		
		public static accessLevel fromInteger(int x) {
	        switch(x) {
	        case 0:
	            return EMPLOYEE;
	        case 1:
	            return SUPERVISOR;
	        case 2:
	        	return PAYROLL_ADMIN;
	        case 3:
	        	return MANAGER;
	        }
	        return EMPLOYEE;
	    }
	}
	
	protected int employeeID;
	
	public int getEmployeeID() {
		return employeeID;
	}



	private int priveligeLevel;
	
	public int getPriveligeLevel() {
		return priveligeLevel;
	}



	/* private String firstName;
	private String lastName;
	
	private String address;
	
	private int jobID;
	
	private double payRate;
	
	private int pto;
	
	private int supervisorID; */
	protected Database db;
	DateTimeFormatter dft = DateTimeFormat.forPattern("yyyy/MM/dd HH:mm:ss");
	DateTimeFormatter dqt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
	DateTimeFormatter df = DateTimeFormat.forPattern("yyyy/MM/dd");
	DateTimeFormatter dq = DateTimeFormat.forPattern("yyyy-MM-dd");
	
	/*public Employee() throws IOException
	{
		Database dbNew = new Database();
		db = dbNew;
		ResultSet r= db.query("SELECT * FROM login");
		String result = "";
		try
		{
			while(r.next()){
				result = r.getString("username");
				System.out.println(result);
			}
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(result);
	} */
	
	public Employee(int employeeID) throws Exception{
		db = new Database();
		this.employeeID = employeeID;
		ResultSet rs= db.select("*", "login", "employeeID = \"" + employeeID + "\"");
		if (rs.next()) {
		priveligeLevel = rs.getInt("levelOfAccess");
		}
	}
	
	public Employee(String userName) throws Exception{
		db = new Database();
		ResultSet rs= db.select("*", "login", "userName = \"" + userName + "\"");
		if (rs.next()) {
		employeeID = rs.getInt("employeeID");
		priveligeLevel = rs.getInt("levelOfAccess");
		}
	}
	
	public Employee(String userName, String password) throws Exception{
		db = new Database();
		ResultSet rs= db.select("*", "login", "userName = '" + userName + "' AND password = '" + password + "'");
		if (rs.next()) {
		employeeID = rs.getInt("employeeID");
		priveligeLevel = rs.getInt("levelOfAccess");
		} else {
			throw new Exception("Invalid Login");
		}
	}
	
	public void logHours(DateTime start, DateTime end) throws Exception {

		DateTime today = new DateTime();
		
		double hours = 0;
		Duration duration = new Duration(start, end);
		hours = duration.getStandardHours() + (duration.getStandardMinutes()-duration.getStandardHours()*60) / 60;
		
		//db.updateInfo("INSERT INTO alerts (description, requestDate, startDate, endDate, employeeID, supervisorID) VALUES ('Request Time Off', '" + today.toString(dft) + "', '" + start.toString(df) + "', '" + end.toString(df) + "', " + employeeID + ", " + this.getSupervisorID() + " )");
//		System.out.println(start.toString(dft));
		db.insert("hours", "dateEntered, hoursWorked, startTime, endTime, employeeID", "'" + today.toString(dft) + "', " + hours + ", '" + start.toString(dft) + "', '" + end.toString(dft) + "', " + employeeID);
	}
	
	public void logRange (DateTime start, DateTime end, Vector<Log> ok) {
		db.delete("hours", "DATE(startTime) BETWEEN '" + start.toString(dft) + "' AND '" + end.toString(dft) +"' AND employeeID = " + employeeID);
		try {
			for(int i = 0; i < ok.size(); ++i)
				logHours(ok.get(i).startTime, ok.get(i).endTime);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public String getFirstName() throws Exception {
		ResultSet rs = db.select("first_name", "employeeInfo", "employeeID = " + employeeID);
		String first_name = "";
		
		if (rs.next()) {
			first_name = rs.getString("first_name");
		} else {
			System.err.println("We didn't have a first name in the database...invalid employeeID?");
		}
		
		return first_name;
	}
	
	public String getName(int eid) throws Exception {
		ResultSet rs = db.select("first_name, last_name", "employeeInfo", "employeeID = " + eid);
		String name = "";
		
		if (rs.next()) {
			name = rs.getString("last_name") + ", " + rs.getString("first_name");
				
		} else {
			System.err.println("We didn't have a first name in the database...invalid employeeID?");
		}
		
		return name;
	}
	
	public String getLastName() throws Exception {
		ResultSet rs = db.select("last_name", "employeeInfo", "employeeID = " + employeeID);
		String last_name = "";
		
		if (rs.next()) {
			last_name = rs.getString("last_name");
		} else {
			System.err.println("We didn't have a first name in the database...invalid employeeID?");
		}
		
		return last_name;
	}
	
	public int getSupervisorID() throws Exception {
		ResultSet rs = db.select("supervisorID", "employeeInfo", "employeeID = " + employeeID);
		int supervisorID = -1;
		
		if (rs.next())
			supervisorID = rs.getInt("supervisorID");
		else
			System.err.println("Is your supervisor null?");
		return supervisorID;
	}
	
	/* public void refreshInfo() throws Exception
	{
		db = new Database();
		ResultSet rs= db.select("levelOfAccess", "login", "employeeID = " + employeeID);
		rs.next();
		priveligeLevel = rs.getInt("levelOfAccess");
		rs= db.query( "SELECT * FROM employeeInfo WHERE employeeID = " + employeeID );
		rs.next();
		firstName = rs.getString("first_name");
		lastName = rs.getString("last_name");
		address = rs.getString("address");
		jobID = rs.getInt("jobID");
		payRate = rs.getDouble("payRate");
		pto = rs.getInt("PTO");
		supervisorID = rs.getInt("supervisorID");
	} */
	
	public void updateHours(DateTime wstart, DateTime wend)
	{
		DateTimeFormatter dft = DateTimeFormat.forPattern("yyyy/MM/dd HH:mm:ss");
		db.delete("hours", "startTime >= '" + wstart.toString(dft) + "' AND " + "startTime < '" + wend.toString(dft) + "'");
		
	}
	
	public void requestTimeOff(DateMidnight start, DateMidnight end, String desc) throws Exception
	{
		DateTime today = new DateTime();
		if(desc.equals(""))
			desc = "Request Time Off";
		//db.updateInfo("INSERT INTO alerts (description, requestDate, startDate, endDate, employeeID, supervisorID) VALUES ('Request Time Off', '" + today.toString(dft) + "', '" + start.toString(df) + "', '" + end.toString(df) + "', " + employeeID + ", " + this.getSupervisorID() + " )");
		db.insert("alerts", "description, requestDate, startDate, endDate, employeeID, supervisorID", "'"+desc+"', '" + today.toString(dqt) + "', '" + start.toString(dq) + "', '" + end.toString(dq) + "', " + employeeID + ", " + getSupervisorID());
	}
	
	public void editPersonalInfo(String fn, String ln, String add)
	{
		//UPDATE My_table
	    //SET field1 = 'updated value'
	    //WHERE field2 = 'N';
		db.updateInfo("UPDATE employeeInfo SET first_name ='" + fn + "', last_name ='" + ln + "', address='" + add + "' WHERE employeeID = " + employeeID );
		//db.update(tables, rows, values)
	}
	
	// Most of the code for a supervisor and above
	public void editSupervisor(int sup)
	{
		//UPDATE My_table
	    //SET field1 = 'updated value'
	    //WHERE field2 = 'N';
		//db.updateInfo("UPDATE employeeInfo SET supervisorID = " + sup + " WHERE employeeID = " + employeeID + "" );
		db.update("employeeInfo", "supervisorID = " + sup, "employeeID = " + employeeID);
		
	}
	
	public void editPTO(int sup)
	{
		//UPDATE My_table
	    //SET field1 = 'updated value'
	    //WHERE field2 = 'N';
		//db.updateInfo("UPDATE employeeInfo SET supervisorID = " + sup + " WHERE employeeID = " + employeeID + "" );
		db.update("employeeInfo", "PTO = " + sup, "employeeID = " + employeeID);
	}
	
	public int getRemainingTimeOff() throws Exception
	{
		ResultSet rs = db.select("PTO", "employeeInfo", "employeeID = " + employeeID);
		
		if (rs.next()) {
			
			return rs.getInt("PTO");
		} else {
			throw new Exception("Query returned no rows.");
		}
	}
	
	public String toString() {
		try {
			return getLastName() + ", " + getFirstName() + "(" + employeeID + ")";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public double getPayRate() throws Exception {
		ResultSet rs = db.select("payRate", "employeeInfo", "employeeID = " + employeeID);
		double payRate = -1;
		
		if (rs.next()) {
			payRate = rs.getDouble("payRate");
		} else {
			System.err.println("We didn't have a pay rate in the database...invalid employeeID?");
		}
		
		return payRate;
	}

	public int getjobID() throws Exception {
		ResultSet rs = db.select("jobID", "employeeInfo", "employeeID = " + employeeID);
		int jobID = -1;
		
		if (rs.next()) {
			jobID = rs.getInt("jobID");
		} else {
			System.err.println("We didn't have a job ID in the database...invalid employeeID?");
		}
		
		return jobID;
	}
	
	public String getJobName() throws Exception {
		
		int jobID = getjobID();
		
		ResultSet rs = db.select("jobName", "pay", "jobID = " + jobID);
		
		String jobName = "";
		
		if (rs.next()) {
			jobName = rs.getString("jobName");
		} else {
			System.err.println("We didn't have a job name in the database...invalid jobID?");
		}
		
		return jobName;
	}

	public String getAddress() throws Exception {
		ResultSet rs = db.select("address", "employeeInfo", "employeeID = " + employeeID);
		String address = "";
		
		if (rs.next()) {
			address = rs.getString("address");
		} else {
			System.err.println("We didn't have a first name in the database...invalid employeeID?");
		}
		
		return address;
	}
	
	public boolean verifyHours(Collection start, Collection end) throws Exception {
		
		return false;
	}
	
	

	public static void main(String[] args)
	{
		String testName = "NorseMaiden";
		int pt;
		DateMidnight start = new DateMidnight(2012,11,25);
		DateMidnight end = new DateMidnight(2012,12,2);
		DateTime wstart = new DateTime("2012-12-01T11:00:00.000-06:00");
//		System.out.println(wstart.toString());
		DateTime wend = new DateTime("2012-12-01T20:00:00.000-06:00");
		try
		{
			Employee e = new Employee(testName);
//			System.out.println(e.toString());
//			pt = e.getRemainingTimeOff();
//			System.out.println(pt);
//			e.refreshInfo();
//			System.out.println(e.toString());
//			e.requestTimeOff(start, end);
//			e.editSupervisor(4);
			e.logHours(wstart, wend);
			e.updateHours(start.toDateTime(), end.toDateTime());
		}
		catch (Exception ex)
		{
			// TODO Auto-generated catch block
			ex.printStackTrace();
		}
	}
	
	public ArrayList<Log> getHours (DateTime start, DateTime end) throws Exception {
		ResultSet r = db.select("*", "hours", "DATE(startTime) BETWEEN '" + start.toString(dft) + "' AND '" + end.toString(dft) +"' AND employeeID = " + employeeID);
		
		DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.0");
		
		ArrayList<Log> eHours = new ArrayList<Log>();
		
		while(r.next())
		{
			Log temp = new Log();
			temp.dateEntered = DateTime.parse(r.getString("dateEntered"), formatter);
			temp.employeeID = r.getInt("employeeID");
			temp.endTime = DateTime.parse(r.getString("endTime"), formatter);
			temp.entryNumber = r.getInt("entryNumber");
			temp.hoursWorked = r.getDouble("hoursWorked");
			temp.startTime = DateTime.parse(r.getString("endTime"), formatter);
			eHours.add(temp);
		}
			
		
		return eHours;
	}
}